package com.stars.questions.deptQuestion.census.mapper;

import com.stars.questions.deptQuestion.census.entity.TotalCensus;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

@Mapper
public interface TotalCensusMapper {
    @Select("SELECT s.question,s.questionid," +
            "  ROUND(((COUNT(s.answer = 10 OR NULL)*10)+(COUNT(s.answer = 8 OR NULL)*8)+(COUNT(s.answer = 6 OR NULL)*6)+(COUNT(s.answer = 4 OR NULL)*4)+(COUNT(s.answer <4 OR NULL)*0))/COUNT(1),2) AS zbdf," +
            "  CONCAT(ROUND(COUNT(s.answer = 10 OR NULL)/(COUNT(s.answer))*100,2),'%') AS excellent," +
            "  CONCAT(ROUND(COUNT(s.answer = 8 OR NULL)/(COUNT(s.answer))*100,2),'%') AS good," +
            "  CONCAT(ROUND(COUNT(s.answer = 6 OR NULL)/(COUNT(s.answer))*100,2),'%') AS quiteGood," +
            "  CONCAT(ROUND(COUNT(s.answer = 4 OR NULL)/(COUNT(s.answer))*100,2),'%') AS commonly," +
            "  CONCAT(ROUND(COUNT(s.answer < 4 OR NULL)/(COUNT(s.answer))*100,2),'%') AS poor," +
            "  CONCAT(ROUND(COUNT(s.answer >=6 OR NULL)/(COUNT(s.answer))*100,2),'%') AS hyszb" +
            "  FROM question_reply_sub s" +
            "  WHERE s.year = #{year} AND s.ROWSTATUS = 1" +
            "                       GROUP BY s.questionid")
    List<TotalCensus> getTotalCensusData(@Param("year") String year);

    @Select("SELECT q.bt,q.id,s.question,s.questionid," +
            "COUNT(s.answer = 10 OR NULL) AS excellent," +
            "COUNT(s.answer = 8 OR NULL) AS good," +
            "COUNT(s.answer = 6 OR NULL) AS quiteGood," +
            "COUNT(s.answer = 4 OR NULL) AS commonly," +
            "COUNT(s.answer < 4 OR NULL) AS poor," +
            "CONCAT(ROUND(COUNT(s.answer = 10 OR NULL)/(COUNT(s.answer))*100,2),'%') AS excellentPer," +
            "CONCAT(ROUND(COUNT(s.answer = 8 OR NULL)/(COUNT(s.answer))*100,2),'%') AS goodPer," +
            "CONCAT(ROUND(COUNT(s.answer = 6 OR NULL)/(COUNT(s.answer))*100,2),'%') AS quiteGoodPer," +
            "CONCAT(ROUND(COUNT(s.answer = 4 OR NULL)/(COUNT(s.answer))*100,2),'%') AS commonlyPer," +
            "CONCAT(ROUND(COUNT(s.answer < 4 OR NULL)/(COUNT(s.answer))*100,2),'%') AS poorPer," +
            "CONCAT(ROUND(COUNT(s.answer >=6 OR NULL)/(COUNT(s.answer))*100,2),'%') AS hyszb,"+
            "ROUND(((COUNT(s.answer = 10 OR NULL)*10)+(COUNT(s.answer = 8 OR NULL)*8)+(COUNT(s.answer = 6 OR NULL)*6)+(COUNT(s.answer = 4 OR NULL)*4)+(COUNT(s.answer <4 OR NULL)*0))/COUNT(1),2) AS zbdf"+
            " FROM question_config q " +
            "LEFT JOIN question_reply r ON q.id = r.CONFIGID " +
            "LEFT JOIN question_reply_sub s ON r.id = s.fid " +
            "WHERE q.ROWSTATUS = 1 AND q.year = #{year} AND r.ROWSTATUS = 1 " +
            "GROUP BY s.questionid")
    List<TotalCensus> getTotalCensusExcelData(String year);

    @Select("SELECT q.bt,s.question," +
            "COUNT(s.answer = 10 OR NULL) AS excellent," +
            "COUNT(s.answer = 8 OR NULL) AS good," +
            "COUNT(s.answer = 6 OR NULL) AS quiteGood," +
            "COUNT(s.answer = 4 OR NULL) AS commonly," +
            "COUNT(s.answer < 4 OR NULL) AS poor," +
            "CONCAT(ROUND(COUNT(s.answer = 10 OR NULL)/(COUNT(s.answer))*100,2),'%') AS excellentPer," +
            "CONCAT(ROUND(COUNT(s.answer = 8 OR NULL)/(COUNT(s.answer))*100,2),'%') AS goodPer," +
            "CONCAT(ROUND(COUNT(s.answer = 6 OR NULL)/(COUNT(s.answer))*100,2),'%') AS quiteGoodPer," +
            "CONCAT(ROUND(COUNT(s.answer = 4 OR NULL)/(COUNT(s.answer))*100,2),'%') AS commonlyPer," +
            "CONCAT(ROUND(COUNT(s.answer < 4 OR NULL)/(COUNT(s.answer))*100,2),'%') AS poorPer" +
            " FROM question_config q " +
            "LEFT JOIN question_reply r ON q.id = r.configid " +
            "LEFT JOIN question_reply_sub s ON r.id = s.fid " +
            "WHERE q.ROWSTATUS = 1 AND r.ROWSTATUS = 1 AND s.questionid = #{questionid} " +
            "GROUP BY s.questionid")
    TotalCensus getTotalBarData(String questionId);

    @Select("SELECT (SELECT COUNT(p.id) FROM question_reply p WHERE p.year = #{year} AND p.ROWSTATUS = 1) AS quesCount" +
            ",CONCAT(ROUND(COUNT(s.answer >=6 OR NULL)/(COUNT(s.answer))*100,2),'%') AS cStore " +
            "FROM question_reply_sub s " +
            "WHERE s.year = #{year} AND s.ROWSTATUS = 1")
    TotalCensus getTotalCensusTopData(String year);

    @Select("SELECT COUNT(s.answer) FROM question_reply_sub s " +
            "WHERE s.year = #{year} AND s.ROWSTATUS = 1 " +
            "GROUP BY s.questionid")
    Integer getTotalAnswer(String year);
}
